USE [QLLVANTAI]
GO

/****** Object:  StoredProcedure [dbo].[LoadTuyenDuong]    Script Date: 09/26/2013 00:53:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE proc [dbo].[LoadTuyenDuong]
as
begin
	
	
	-- select MaTuyenDuong, DiemXuatPhat va insert vao temp
	declare @temp table(MaTuyenDuong int, TenTuyenDuong nvarchar(50))
	
	declare diemxuatphat cursor
		for select Ma_TuyenDuong, Diem_XuatPhat from TuyenDuong
		
	open diemxuatphat
	
	declare @matuyenduong int
	declare @diemxuatphat nvarchar(50)
	
	FETCH NEXT FROM diemxuatphat INTO @matuyenduong, @diemxuatphat
	while @@FETCH_STATUS=0       
	begin
		insert into @temp(MaTuyenDuong, TenTuyenDuong) values(@matuyenduong, @diemxuatphat)
		FETCH NEXT FROM diemxuatphat INTO @matuyenduong, @diemxuatphat
	end
	
	close diemxuatphat
	DEALLOCATE diemxuatphat
	
	
	-- noi diem ket thuc vao temp
	declare diemden cursor
		for select Ma_TuyenDuong, Diem_Den from TuyenDuong
		
	open diemden
	declare @matd int
	declare @diemden nvarchar(50)
	FETCH NEXT FROM diemden INTO @matd, @diemden
	while @@FETCH_STATUS=0       
	begin
		Update @temp Set TenTuyenDuong = TenTuyenDuong + ' - ' + @diemden where MaTuyenDuong = @matd
		FETCH NEXT FROM diemden INTO @matd, @diemden
	end
	
	close diemden
	DEALLOCATE diemden
	
	-- select bang temp
	select * from @temp
end
GO

